package com.jian.exceldaoru.service.impl;

import com.jian.exceldaoru.controller.ExcelBean;
import com.jian.exceldaoru.controller.StringUtil;
import com.jian.exceldaoru.controller.TableEnum;
import com.jian.exceldaoru.service.ExcelService;
import com.jian.exceldaoru.service.ExcelValidate;
import org.apache.commons.codec.binary.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * Created by lijian on 2020/10/20 16:17
 */
@Service
public class ExcelServiceImpl implements ExcelService {

    @Override
    public ExcelBean excelToData(InputStream inputStream, ExcelValidate excelValidate) {
        ExcelBean excelBean = new ExcelBean();
        List<String> detailList = new ArrayList<>();
        List<List<String>> allDetailList = new ArrayList<>();
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
            inputStream.close();
            //工作表对象
            Sheet sheet = workbook.getSheetAt(0);
            //总行数
            int rowLength = sheet.getLastRowNum();
            if(rowLength<1){
                return ExcelBean.createFail(excelBean,"请确认是否有导入的内容");
            }
            //工作表的列
            Row row = sheet.getRow(0);
            //总列数
            int colLength = row.getLastCellNum();
            if(colLength<1){
                return ExcelBean.createFail(excelBean,"请确认是否有导入的明细内容");
            }
            if(excelValidate.getColNum()!=null&&colLength!=excelValidate.getColNum()){
                return ExcelBean.createFail(excelBean,"请确认导入的明细内容是否规范");
            }
            //得到指定的单元格
            Cell cell = row.getCell(0);
            excelBean = getTableInfo(sheet,rowLength,row,colLength,cell,excelValidate);
            detailList = getDetailList(sheet,row,colLength,cell,excelBean);
            for (int i=1;i< detailList.size();i++) {
                List<String> list =  new ArrayList<>();
                String s = detailList.get(i);
                if(s.length()>0){
                    s = s.substring(0,s.length()-1);
                    list = StringUtil.splitString(s,",");
                    if(!CollectionUtils.isEmpty(list)&&list.get(0).isEmpty()&&!CollectionUtils.isEmpty(allDetailList)){
                        list.set(0,allDetailList.get(i-1-1).get(0));
                    }
                }
                allDetailList.add(list);
            }
            excelBean.setDataList(allDetailList);
        }catch (Exception e) {
            e.printStackTrace();
        }
        return excelBean;
    }
    private static ExcelBean getTableInfo(Sheet sheet,
                                          int rowLength,
                                          Row row,
                                          int colLength,
                                          Cell cell,
                                          ExcelValidate excelValidate){
        ExcelBean excelBean = new ExcelBean();
        for (int i = 1; i <= rowLength; i++) {
            row = sheet.getRow(i);
            if(row!=null) {
                for (int j = 0; j < colLength; j++) {
                    cell = row.getCell(j);
                    //                    System.out.print(cell + ",");
                    if (cell != null) {
                        cell.setCellType(CellType.STRING);
                        String data = cell.getStringCellValue();
                        data = data.trim();
                        if (!data.isEmpty()) {
                            // 确定表头表尾的数据
                            if(data.indexOf(":")!=-1||data.indexOf("：")!=-1){
                                List<String> cellDatas = StringUtil.subStringBy(data);
                                Map<String,String> templateInfo = ExcelValidate.getTemplate(excelValidate);
                                for (Map.Entry<String, String> entry : templateInfo.entrySet()) {
                                    String key = entry.getKey();
                                    String value = entry.getValue();
                                    String attr = cellDatas.get(0);
                                    if(StringUtils.equals(value,attr)){
                                        switch (TableEnum.toType(key)) {
                                            case STATISTICAL_TIME:
                                                excelBean.setStatisticalTime(cellDatas.get(1));
                                                excelBean.setStatisticalTimeRow(i);
                                                break;
                                            case STATISTICAL_TYPE:
                                                excelBean.setStatisticalType(cellDatas.get(1));
                                                excelBean.setStatisticalTypeRow(i);
                                                break;
                                            case LINE_NAME:
                                                excelBean.setLineName(cellDatas.get(1));
                                                excelBean.setLineNameRow(i);
                                                break;
                                            case TABULATION_TIME:
                                                excelBean.setTabulationTime(cellDatas.get(1));
                                                excelBean.setTabulationTimeRow(i);
                                                break;
                                            case TABULATOR:
                                                excelBean.setTabulator(cellDatas.get(1));
                                                excelBean.setTabulatorRow(i);
                                                break;
                                            case TABULATING_UNIT:
                                                excelBean.setTabulatingUnit(cellDatas.get(1));
                                                excelBean.setTabulatingUnitRow(i);
                                                break;
                                            default:
                                                break;
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
        excelBean = getDetailStartEndRow(excelBean,excelValidate);
        excelBean.setDetailTitleLineNum(excelValidate.getDetailTitleLineNum());
        return excelBean;
    }
    private static ExcelBean getDetailStartEndRow(ExcelBean excelBean,ExcelValidate excelValidate){
        Map<String,String> templateInfo = ExcelValidate.getTemplate(excelValidate);
        for (Map.Entry<String, String> entry : templateInfo.entrySet()) {
            String key = entry.getKey();
            String value = entry.getValue();
            if(value.equals(excelValidate.getDetailStart())){
                switch (TableEnum.toType(key)) {
                    case STATISTICAL_TIME:
                        excelBean.setStartRow(excelBean.getStatisticalTimeRow());
                        break;
                    case STATISTICAL_TYPE:
                        excelBean.setStartRow(excelBean.getStatisticalTypeRow());
                        break;
                    case LINE_NAME:
                        excelBean.setStartRow(excelBean.getLineNameRow());
                        break;
                    case TABULATION_TIME:
                        excelBean.setStartRow(excelBean.getTabulationTimeRow());
                        break;
                    case TABULATOR:
                        excelBean.setStartRow(excelBean.getTabulatorRow());
                        break;
                    case TABULATING_UNIT:
                        excelBean.setStartRow(excelBean.getTabulatingUnitRow());
                        break;
                    default:
                        break;
                }
            }
            if(value.equals(excelValidate.getDetailEnd())){
                switch (TableEnum.toType(key)) {
                    case STATISTICAL_TIME:
                        excelBean.setEndRow(excelBean.getStatisticalTimeRow());
                        break;
                    case STATISTICAL_TYPE:
                        excelBean.setEndRow(excelBean.getStatisticalTypeRow());
                        break;
                    case LINE_NAME:
                        excelBean.setEndRow(excelBean.getLineNameRow());
                        break;
                    case TABULATION_TIME:
                        excelBean.setEndRow(excelBean.getTabulationTimeRow());
                        break;
                    case TABULATOR:
                        excelBean.setEndRow(excelBean.getTabulatorRow());
                        break;
                    case TABULATING_UNIT:
                        excelBean.setEndRow(excelBean.getTabulatingUnitRow());
                        break;
                    default:
                        break;
                }
            }
        }
        return excelBean;
    }
    private static List<String> getDetailList(Sheet sheet,Row row,int colLength,Cell cell,ExcelBean excelBean){
        List<String> detailList = new ArrayList<>();
        for (int i = excelBean.getStartRow()+excelBean.getDetailTitleLineNum(); i <excelBean.getEndRow(); i++) {
            row = sheet.getRow(i);
            if (row != null) {
                StringBuilder sb = new StringBuilder();
                for (int j = 0; j < colLength; j++) {
                    cell = row.getCell(j);
                    if (cell != null) {
                        cell.setCellType(CellType.STRING);
                        String data = cell.getStringCellValue();
                        data = data.trim();
                        sb.append(data).append(",");
                    }
                }
                detailList.add(sb.toString());
            }
        }
        return detailList;
    }
}
